﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using XeBus.BusinessObject;
using System.Data;
using System.Data.SqlClient;

namespace XeBus.DataAccess
{
    public class TimKiemDAO
    {
        private ConnectData _conn;
        private string sql;

        public TimKiemDAO()
        {
            _conn = new ConnectData();
        }

        // Hàm tìm kiếm
        public DataTable TimKiem(string sql)
        {
            SqlParameter[] param = new SqlParameter[1];

            param[0] = new SqlParameter("SQL", SqlDbType.NVarChar);
            param[0].Value = sql;

            return _conn.ExecuteDataTable("TIM_KIEM", param);
        }

        public DataTable LayDuLieuTheoID(string id)
        {

            sql = "SELECT *, RIGHT(CONVERT(VARCHAR, TG_Bat_Dau, 100),7) Bat_Dau,RIGHT(CONVERT(VARCHAR, TG_Ket_Thuc, 100),7) Ket_Thuc FROM TuyenXeBuyt WHERE Ma_Tuyen=" + id + ";";
            return _conn.ExecuteSelectQuery(sql, null);
        }

        public DataTable LayDuLieu(string id)
        {
            sql = "SELECT td.*,cd.Ten_Duong FROM TramDung td INNER JOIN ConDuong cd ON cd.Ma_Con_Duong= td.Ma_Con_Duong WHERE Ma_Tram_Dung=" + id;
            return _conn.ExecuteSelectQuery(sql, null);
        }

        public DataTable So_Hieu_Tuyen()
        {
            return _conn.ExecuteSelectQuery("SELECT So_Hieu_Tuyen FROM TuyenXeBuyt", null);
        }

        public DataTable Select_LoTrinh_By_SoHieuTuyen(string so_hieu_tuyen)
        {
            sql = "SELECT Ma_Lo_Trinh, Huong_Di FROM LoTrinh l WHERE l.Ma_Tuyen=(SELECT Ma_Tuyen FROM TuyenXeBuyt t WHERE t.So_Hieu_Tuyen=" + so_hieu_tuyen + ")";
            return _conn.ExecuteSelectQuery(sql, null);
        }

        public DataTable Select_TenDuong_ByMaLoTrinh(string ma_lo_trinh)
        {
            sql = "SELECT Ten_Duong FROM ConDuong c INNER JOIN ConDuong_LoTrinh cl On c.Ma_Con_Duong=cl.Ma_Con_Duong AND c.Ma_Con_Duong IN (SELECT Ma_Con_Duong FROM ConDuong_LoTrinh cl WHERE  cl.Ma_Lo_Trinh=" + ma_lo_trinh + ") ORDER BY cl.So_Thu_Tu";
            return _conn.ExecuteSelectQuery(sql, null);
        }
    }
}
